R& Base de Données

tidyverse

if (!"tidyverse"%in%rownames(installed.packages())) {
  install.packages("tidyverse")
}
library(tidyverse)
tidyverse_update()
library(tidyverse)
tidyverse_conflicts()
tibble(
  x = 1:5, 
  y = 1, 
  z = x ^ 2 + y
)
data.frame(
  x = 1:5, 
  y = 1, 
  z = x ^ 2 + y
)
data.frame(`1`= 1:3)
tibble(
  `;)` = 1:5, 
  `42` = "1", 
  `€` = `;)` ^ 2 + as.numeric(`42`)
)
as.data.frame(mtcars)
print(as.data.frame(mtcars))
as_tibble(mtcars)
print(as_tibble(mtcars))
as_tibble(rownames_to_column(mtcars))
print(as_tibble(rownames_to_column(mtcars)))
options(tibble.print_max = n, tibble.print_min = m, dplyr.print_min = p)
print(x = DF, n = n, width = p))
View(mtcars)
mtcars$mpg
mtcars[["mpg"]]
mtcars[, "mpg"]
as_tibble(mtcars)$mpg
as_tibble(mtcars)[["mpg"]]
as_tibble(mtcars)[, "mpg"]
tb <- as_tibble(mtcars)
class(tb)
class(as.data.frame(tb))
df <- data.frame(abc = 1, xyz = "a")
df$xyz
df[["xyz"]]
df[, "xyz"]
df[, c("abc", "xyz")]
x <- "abc"
class(dta)
df1 <- data.frame(abc = 1, xyz = "a")
df2 <- as_tibble(df1)
df1$xyz
df1[["xyz"]]
df1[, "xyz"]
df1[, c("abc", "xyz")]
x <- "abc"
df1[, x]
df1[[x]]
x <- "abc"
df2[, x]
df2[[x]]
dta <- tibble(
  `1` = 1:10,
  `2` = `1` * 2 + rnorm(length(`1`))
)
dta <- tibble(
  `1` = 1:10,
  `2` = `1` * 2 + rnorm(length(`1`))
)
dta$`1`
plot(dta$`1`, dta$`2`)
ggplot(data = dta, aes(x = `1`, y = `2`)) + geom_point(colour = "white")
dta <- tibble(
  `1` = 1:10,
  `2` = `1` * 2 + rnorm(length(`1`)),
  `3` = `2`/ `1`
)
dta <- tibble(
  "un" = 1:10,
  "deux" = un * 2 + rnorm(length(un)),
  "trois" = deux / un
)
vignette(package = "tibble")
library(pryr)
library(tidyverse)
tidyverse_conflicts()
x <- rnorm(25)
x1 <- sqrt(x)
x2 <- na.exclude(x1)
x3 <- x2<1
x4 <- table(x3)
x5 <- prop.table(x4)
x6 <- which.max(x5)
x7 <- names(x6)
mtcars2 <- mtcars %>% mutate(cyl_fac = factor(cyl))

pryr::object_size(mtcars)
pryr::object_size(mtcars2)
pryr::object_size(mtcars, mtcars2)
x <- rnorm(25)

x <- sqrt(x)
x <- na.exclude(x)
x <- x<1
x <- table(x)
x <- prop.table(x)
x <- which.max(x)
x <- names(x)
x <- rnorm(25)

names(
  which.max(
    prop.table(
      table(
        na.exclude(
          sqrt(x)
        )<1
      )
    )
  )
)

names(which.max(prop.table(table(na.exclude(sqrt(x))<1))))
x %>% 
  sqrt() %>% 
  na.exclude() %>% 
  `<`(1) %>% 
  table() %>% 
  prop.table() %>% 
  which.max() %>% 
  names()
vignette(package = "magrittr")
library(readxl)
library(tidyverse)
tidyverse_conflicts()
locale()
ratings <- read.csv(
  file = "./materials/ratings.csv"
)
str(ratings[0, ])
ratings <- read_csv(
  file = "./materials/ratings.csv"
)
read.csv(
  text = "a,b,c
    1,2,3
    4,5,6"
)
read_csv(
  "a,b,c
  1,2,3
  4,5,6"
)
read_csv("Une ligne de métadonnées
  x,y,z
  1,2,3", skip = 1)
read_csv("# Une ligne de commentaires
  x,y,z
  1,2,3", comment = "#")
read_csv("1,2,3\n4,5,6", col_names = FALSE)
read_csv("1,2,3\n4,5,6", col_names = c("a", "b", "c"))
read_csv("a,b,c\n1,2,3\n4,.,6", na = ".")
read_csv(
  "a,b,c\n1,x,2018-05-03\n4,y,2018-05-04", 
  col_types = list(col_integer(), col_character(), col_date(format = "%Y-%m-%d"))
)
"x,y\n1,'a,b'"
read_csv("a,b\n1,2,3\n4,5,6")
read_csv("a,b,c\n1,2\n1,2,3,4")
read_csv("a,b\n\"1")
read_csv("a,b\n1,2\na,b")
read_csv("a;b\n1;3")
read_delim(file = path, delim = "|")
read_csv("x,y\n1,'a,b'", quote = "\'")
read_delim("x,y\n1,'a,b'", delim = ",", quote = "\'")
read_csv("a,b\n1,2,3\n4,5,6")
read_csv("a,b,c\n1,2\n1,2,3,4")
read_csv("a,b\n\"1")
read_csv("a,b\n1,2\na,b")
read_csv("a;b\n1;3")
readr::write_csv(x = ratings, path = "ratings.csv")
writexl::write_xlsx(x = ratings, path = "ratings.xlsx")
library(tidyverse)
tidyverse_conflicts()
ratings <- read_csv(file = "./materials/ratings.csv")
filter(ratings, `Your Rating` >= 9, `IMDb Rating` >= 9)

ratings %>% 
  filter(`Your Rating` >= 9 & `IMDb Rating` >= 9)

ratings %>% 
  filter(`Num Votes`==max(`Num Votes`))
ratings %>% 
  filter(`Num Votes`==max(`Num Votes`))
ratings %>% 
  filter(grepl(pattern = "shark", x = Title, ignore.case = TRUE))
ratings %>% 
  filter(`Num Votes`==max(`Num Votes`))
ratings %>% 
  filter(grepl(pattern = "shark", x = Title, ignore.case = TRUE))
ratings %>% 
  filter(`Date Rated`>="2018-04-25" & `Date Rated`<="2018-05-03")
ratings %>% 
  filter(`Num Votes`==max(`Num Votes`))
ratings %>% 
  filter(grepl(pattern = "shark", x = Title, ignore.case = TRUE))
ratings %>% 
  filter(`Date Rated`>="2018-04-25" & `Date Rated`<="2018-05-03")
ratings %>% 
  filter(between(`Date Rated`, as.Date("2018-04-25"), as.Date("2018-05-03")))
ratings %>% 
  arrange(desc(`Date Rated`))

ratings %>% 
  arrange(desc(`IMDb Rating`), desc(`Your Rating`))
ratings %>% 
  arrange(Title)
ratings %>% 
  arrange(desc(`Date Rated`)) %>% 
  select(Title, ends_with("Rating"))

ratings %>% 
  select(contains("Date"))
ratings %>% 
  select(4, 2, 3, 12)
ratings %>% 
  arrange(desc(`Date Rated`)) %>% 
  mutate(Rating_is_better = `Your Rating`>=`IMDb Rating`) %>% 
  select(Title, `Your Rating`, `IMDb Rating`, Rating_is_better)
ratings %>% 
  mutate(`Avg Rating` = (`Your Rating`+`IMDb Rating`)/2)
ratings %>% 
  mutate(
    Month = lubridate::month(`Date Rated`),
    Day = lubridate::day(`Date Rated`)
  )
read_csv(file = "./materials/ratings.csv") %>%  
  summarise(
    n_movies = n(),
    my_average_rating = mean(`Your Rating`),
    oldest_rating = min(`Date Rated`),
    newest_rating =  max(`Date Rated`)
  )

ratings %>% 
  summarise(
    mean = mean(`IMDb Rating`),
    sd = sd(`IMDb Rating`),
    min = min(`IMDb Rating`),
    max = max(`IMDb Rating`)
  )
ratings %>% 
  summarise_at(
    .vars = vars(`IMDb Rating`), 
    .funs = funs(mean, sd, min, max)
  )
ratings %>% 
  summarise(
    ndays = max(`Date Rated`)-min(`Date Rated`)
  )
read_csv(file = "./materials/ratings.csv") %>% 
  mutate(
    Year = lubridate::year(`Date Rated`)
  ) %>% 
  filter(Year!="2014") %>% 
  group_by(Year) %>% 
  summarise(
    n_movies = n(),
    my_average_rating = mean(`Your Rating`),
    users_average_rating = mean(`IMDb Rating`)
  ) %>% 
  arrange(desc(Year))
read_csv(file = "./materials/ratings.csv") %>% 
  mutate(
    Year = lubridate::year(`Date Rated`)
  ) %>% 
  filter(Year=="2017") %>% 
  group_by(`Your Rating`) %>% 
  summarise(
    n_movies = n(),
    runtime_all = sum(`Runtime (mins)`)
  ) %>% 
  arrange(desc(runtime_all))
vignette(package = "dplyr")
library(DBI)
library(odbc)
tidyverse_conflicts()
CREATE ROLE test_user LOGIN password 'test_pwds';
CREATE DATABASE test_data;
ALTER DATABASE test_data OWNER TO 'test_user';
odbcListDrivers() %>% 
  filter(attribute=="Description") %>% 
  select(-attribute)
con <- dbConnect(
  drv = odbc(),
  driver = "PostgreSQL ODBC Driver(UNICODE)",
  server = "localhost",
  port = 5432,
  database = "postgres",
  uid = "postgres",
  password = "password"
)
con <- dbConnect(
  drv = odbc(),
  driver = "PostgreSQL ODBC Driver(UNICODE)",
  server = "localhost",
  port = 5432,
  database = "postgres",
  uid = "postgres",
  password = rstudioapi::askForPassword("Mot de passe :")
)
dbSendQuery(con, SQL("CREATE ROLE test_user LOGIN password 'test_pwd';"))
dbSendQuery(con, SQL("CREATE DATABASE test_data;"))
dbSendQuery(con, SQL("ALTER DATABASE test_data OWNER TO test_user;"))
dbDisconnect(con)
con <- dbConnect(
  drv = odbc(),
  driver = "PostgreSQL ODBC Driver(UNICODE)",
  server = "localhost",
  database = "test_data",
  port = 5432,
  uid = "test_user",
  password = "test_pwd"
)
dbListTables(con)
mtcars %>% 
  rownames_to_column(var = "car") %>% 
  mutate(const = gsub(" .*", "", car)) %>% 
  select(const, everything()) %>% 
  dbWriteTable(con, "mtcars", .)

dbListTables(con)
dbListFields(con, "mtcars")
dbReadTable(con, "mtcars") %>% 
  as_tibble()
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE const='Merc'")
dbFetch(res)
dbClearResult(res)
dbDisconnect(con)
con <- dbConnect(
  drv = odbc(),
  driver = "PostgreSQL ODBC Driver(UNICODE)",
  server = "localhost",
  database = "postgres",
  port = 5432,
  uid = "postgres",
  password = "password"
)
dbSendQuery(con, SQL("CREATE ROLE data_user LOGIN password 'data_pwd';"))
dbSendQuery(con, SQL("CREATE DATABASE datawarehouse;"))
dbSendQuery(con, SQL("ALTER DATABASE datawarehouse OWNER TO data_user;"))
dbDisconnect(con)
ratings <- read_csv(file = "./materials/ratings.csv") %>% 
  `colnames<-`(gsub(" ", "", colnames(.)))
con <- dbConnect(
  drv = odbc(),
  driver = "PostgreSQL ODBC Driver(UNICODE)",
  server = "localhost",
  database = "datawarehouse",
  port = 5432,
  uid = "data_user",
  password = "data_pwd"
)
dbWriteTable(con, "ratings", ratings)
dbListFields(con, 'ratings')
dbSendQuery(
  conn = con, 
  statement = SQL(
    'SELECT "YourRating", COUNT(*) as count FROM ratings GROUP BY "YourRating";'
  )
) %>% 
  dbFetch() %>% 
  as_tibble()
dbSendQuery(
  conn = con, 
  statement = SQL(
    'SELECT * 
      FROM ratings 
      WHERE "DateRated">=\'2017-01-01\' AND "DateRated"<=\'2017-12-31\';'
  )
) %>% 
  dbFetch() %>% 
  dbWriteTable(con, "ratings2017", .)

dbListTables(con)
vignette(package = "DBI")
library(DBI)
library(odbc)
library(tidyverse)
library(dbplyr)
tidyverse_conflicts()
con <- dbConnect(RSQLite::SQLite(), path = ":memory:")
con <- dbConnect(
  drv = odbc(),
  driver = "PostgreSQL ODBC Driver(UNICODE)",
  server = "localhost",
  database = "datawarehouse",
  port = 5432,
  uid = "data_user",
  password = "data_pwd"
)
ratings <- read_csv(file = "./materials/ratings.csv") %>% 
  `colnames<-`(gsub(" ", "", colnames(.))) %>% 
  mutate(Year = lubridate::year(DateRated))
copy_to(
  dest = con, 
  df = ratings, 
  name = "ratings_idx",
  temporary = FALSE, 
  indexes = list(
    "DateRated", 
    "YourRating", 
    "Title", 
    "Year"
  ),
  overwrite = TRUE
)
ratings_db <- tbl(con, "ratings")
class(ratings_db)
ratings_db
date_sevendaysago <- (Sys.Date()-7)

ratings_db %>% 
  filter(DateRated > date_sevendaysago) %>% 
  select(Title, DateRated, YourRating, IMDbRating)
ratings_db %>% 
  group_by(Year) %>% 
  summarise(
    N = n(),
    AvgRating = mean(YourRating)
  ) %>% 
  arrange(Year)
ratings_summary <- ratings %>% 
  group_by(Year) %>% 
  summarise(
    N = n(),
    AvgRating = mean(YourRating)
  ) %>% 
  arrange(Year)
str(ratings_summary, 1)
ratings_summary_db <- ratings_db %>% 
  group_by(Year) %>% 
  summarise(
    N = n(),
    AvgRating = mean(YourRating)
  ) %>% 
  arrange(Year)
str(ratings_summary_db, 1)
ratings_summary_db %>% 
  print()
ratings_summary_db %>% 
  collect()
 ratings_summary_db %>% 
  show_query()
translate_sql(head(ratings_db))
translate_sql(as.numeric(x))
translate_sql(as.character(x))
translate_sql(x^2)
tbl(con, sql('select 1 as x')) %>%
  mutate(sqr = x^2)
translate_sql(mean(x))
translate_sql(mean(x, trim = 0.05))
translate_sql(mean(x, na.rm = TRUE))
ratings_summary <- ratings_summary_db %>% 
  collect()
class(ratings_summary)
ratings_summary_db %>% head(1)
ratings_summary_db %>% tail()
ratings_summary_db %>% nrow()
tally(ratings_summary_db)
explain(ratings_summary_db)
con <- dbConnect(
  drv = odbc(),
  driver = "PostgreSQL ODBC Driver(UNICODE)",
  server = "localhost",
  database = "datawarehouse",
  port = 5432,
  uid = "data_user",
  password = "data_pwd"
)
copy_to(con, mtcars, "mtcars", temporary = TRUE, overwrite = TRUE)
dbListTables(con)
copy_to(con, mtcars, "mtcars", temporary = FALSE, overwrite = TRUE)
dbListTables(con)
dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)
dbListTables(con)
mtcars %>% 
  rownames_to_column(var = "car") %>% 
  mutate(const = gsub(" .*", "", car)) %>% 
  select(const, everything()) %>% 
  dbWriteTable(con, "mtcars", ., overwrite = TRUE)
library(nycflights13)
dbWriteTable(con, "flights", flights)
dbWriteTable(con, "airports", airports)
dbWriteTable(con, "planes", planes)
dbWriteTable(con, "weather", weather)
dbWriteTable(con, "airlines", airlines)

dbListTables(con)
library(nycflights13)
nycflights13_db <- ls("package:nycflights13") %>% 
  lapply(., function(df) { 
    copy_to(
      df = get(df), 
      dest = con, 
      name = df, 
      overwrite = TRUE, 
      temporary = TRUE
    ) 
  }) %>% 
  `names<-`(ls("package:nycflights13"))
dbListTables(con)
longest_air_time_db <- nycflights13_db[["flights"]] %>%
  inner_join(nycflights13_db[["planes"]], by = "tailnum") %>%
  group_by(manufacturer) %>% 
  summarise(total_air_time = sum(air_time)/60) %>% 
  arrange(desc(total_air_time)) %>%
  select(manufacturer, total_air_time) %>% 
  head(3)
longest_air_time_db
longest_air_time_db %>% show_query()
nycflights13::weather %>% 
  group_by(origin, year) %>%  
  summarise(avg_temp = mean(temp))
nycflights13_db[["weather"]] %>% 
  group_by(origin, year) %>% 
  summarise(avg_temp = mean(temp))
nycflights13::weather %>% 
  group_by(origin, year) %>%  
  summarise(avg_temp = mean(temp))
nycflights13_db[["weather"]] %>% 
  group_by(origin, year) %>% 
  summarise(avg_temp = mean(temp))
nycflights13_db[["flights"]] %>% 
  filter(dest %like% 'A%') %>% 
  summarise(count = n_distinct(dest))
nycflights13_db[["flights"]] %>%
  mutate(origin_dest = origin %|| '-' ||% dest) %>%
  select(origin, dest, origin_dest)
nycflights13_db[["flights"]] %>%
  mutate(origin_dest = CONCAT(origin, "-", dest)) %>%
  select(origin, dest, origin_dest)
nycflights13_db[["flights"]] %>%
  mutate(origin_dest = paste(origin, dest, sep = "-")) %>%
  select(origin, dest, origin_dest)
nycflights13_db[["flights"]] %>%
  mutate(origin_dest = origin %|| '-' ||% dest) %>%
  select(origin, dest, origin_dest)
nycflights13_db[["flights"]] %>%
  mutate(origin_dest = CONCAT(origin, "-", dest)) %>%
  select(origin, dest, origin_dest)
nycflights13_db[["flights"]] %>%
  mutate(origin_dest = paste(origin, dest, sep = "-")) %>%
  select(origin, dest, origin_dest)
nycflights13_db[["flights"]] %>%
  mutate(origin_dest = origin %|| '-' ||% dest) %>%
  select(origin, dest, origin_dest)
nycflights13_db[["flights"]] %>%
  mutate(origin_dest = CONCAT(origin, "-", dest)) %>%
  select(origin, dest, origin_dest)
nycflights13_db[["flights"]] %>%
  mutate(origin_dest = paste(origin, dest, sep = "-")) %>%
  select(origin, dest, origin_dest)
dbSendQuery(con, "CREATE SCHEMA nycflights13;")
dbWriteTable(con, SQL("nycflights13.flights"), flights)
tbl(con, "mtcars") %>% head()
tbl(con, in_schema("public", "mtcars")) %>% head()
tbl(con, in_schema("nycflights13", "flights")) %>% head(1)
tbl(con, in_schema("nycflights13", "weather")) %>% head(1)
library(xml2)
library(rvest)
library(tidyverse)
tidyverse_conflicts()
read_html()
html_nodes()
imdb_xml %>% 
  html_nodes("h1") %>% 
  html_text()
rpo_cast_url <- "https://www.imdb.com/title/tt1677720/fullcredits/"
rpo_cast_list <- read_html(rpo_cast_url) %>% 
  html_nodes("table.cast_list")
rpo_character_list <- rpo_cast_list %>% 
  html_nodes("td.character")
html_text(rpo_character_list) %>% head(2)
rpo_character_list %>% 
  html_text() %>% 
  gsub("\n", "", .) %>% 
  gsub(" +", " ", .) %>% 
  gsub("^ (.*) $", "\\1", .) %>% 
  head(2)
cast_tbl <- tibble(
  Actor = read_html(rpo_cast_url) %>% 
    html_nodes("td.itemprop") %>% 
    html_nodes("span.itemprop") %>% 
    html_text(),
  Character = rpo_character_list %>% 
    html_text() %>% 
    gsub("\n", "", .) %>% 
    gsub(" +", " ", .) %>% 
    gsub("^ (.*) $", "\\1", .)
)

cast_tbl %>% 
  head(3)
ratings <- read_csv("materials/ratings.csv") %>% 
  `colnames<-`(gsub(" ", "", colnames(.))) %>% 
  mutate(
    Year = lubridate::year(DateRated),
    Month = lubridate::month(DateRated),
    Day = lubridate::wday(DateRated)
  )
con <- dbConnect(
  drv = odbc(),
  driver = "PostgreSQL ODBC Driver(UNICODE)",
  server = "localhost",
  database = "postgres",
  port = 5432,
  uid = "postgres",
  password = "password"
)
dbSendQuery(con, SQL("CREATE ROLE imdb_user LOGIN password 'imdb_pwd';"))
dbSendQuery(con, SQL("CREATE DATABASE movies;"))
dbSendQuery(con, SQL("ALTER DATABASE movies OWNER TO imdb_user;"))
dbDisconnect(con)
con <- dbConnect(
  drv = odbc(),
  driver = "PostgreSQL ODBC Driver(UNICODE)",
  server = "localhost",
  database = "movies",
  port = 5432,
  uid = "imdb_user",
  password = "imdb_pwd"
)
ratings_db <- copy_to(
  dest = con, 
  df = ratings, 
  names = "ratings", 
  overwrite = TRUE,
  temporary = FALSE
)
cast_list <- ratings_db %>% 
  filter(Title %like% "Ready Player One") %>% 
  collect() %>% 
  .[["URL"]] %>% 
  paste0(., "fullcredits/") %>% 
  read_html() %>% 
  html_nodes("table.cast_list")
cast_tbl <- tibble(
  Actor = cast_list %>% 
    html_nodes("td.itemprop") %>% 
    html_nodes("span.itemprop") %>% 
    html_text(),
  Character = cast_list %>% 
    html_nodes("td.character") %>% 
    html_text() %>% 
    gsub("\n", "", .) %>% 
    gsub(" +", " ", .) %>% 
    gsub("^ (.*) $", "\\1", .)
)
get_cast <- function(x) {
  x %>% 
    paste0(., "fullcredits/") %>% 
    read_html() %>% 
    html_nodes("table.cast_list") %>% 
    (function(node) {
      cast_tbl <- tibble(
        URL = x,
        Actor = node %>% 
          html_nodes("td.itemprop") %>% 
          html_nodes("span.itemprop") %>% 
          html_text(),
        Character = node %>% 
          html_nodes("td.character") %>%
          html_text() %>% 
          gsub("\n", "", .) %>% 
          gsub(" +", " ", .) %>% 
          gsub("^ (.*) $", "\\1", .)
      )
    }) %>% 
    return()
}
get_cast("https://www.imdb.com/title/tt0100240/")
casting <- ratings_db %>% 
  select(URL) %>% 
  collect() %>% 
  head() %>% 
  mutate(
    Casting = map(URL, get_cast)
  )
casting
movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>% 
  read_html() %>% 
  html_nodes("div#ratings-container.lister-list") %>% 
  html_nodes("div.lister-item.mode-detail")
title <- movies_list %>% 
  html_nodes("h3.lister-item-header") %>% 
  html_text() %>% 
  gsub("\n", "", .) %>% 
  gsub(" [0-9]+.", "", .) %>% 
  gsub(" +", " ", .) %>% 
  gsub("^ (.*) $", "\\1", .)
movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>% 
  read_html() %>% 
  html_nodes("div#ratings-container.lister-list") %>% 
  html_nodes("div.lister-item.mode-detail")
genres <- movies_list %>% 
  html_nodes("span.genre") %>% 
  html_text() %>% 
  gsub("\n", "", .) %>% 
  gsub(" +", " ", .) %>% 
  gsub("^ (.*) $", "\\1", .)
movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>% 
  read_html() %>% 
  html_nodes("div#ratings-container.lister-list") %>% 
  html_nodes("div.lister-item.mode-detail")
runtime <- movies_list %>% 
  html_nodes("span.runtime") %>% 
  html_text() %>% 
  gsub("\n", "", .) %>% 
  gsub(" +", " ", .) %>% 
  gsub("^ (.*) $", "\\1", .)
movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>% 
  read_html() %>% 
  html_nodes("div#ratings-container.lister-list") %>% 
  html_nodes("div.lister-item.mode-detail")
rating <- movies_list %>% 
  html_nodes("div.ipl-rating-widget") %>% 
  html_nodes("div.ipl-rating-star.small") %>% 
  html_text("span.ipl-rating-star__rating") %>% 
  unique() %>% 
  gsub("\n", "", .) %>% 
  gsub(" +", " ", .) %>% 
  gsub("^ (.*) $", "\\1", .) %>% 
  as.numeric() %>% 
  (function(.x) {if (length(.x)!=2) {c(NA, NA)} else {.x}})
get_movies_info <- function(x) {
  c(
    x %>% html_nodes("h3.lister-item-header") %>% html_text() %>% gsub("\n", "", .) %>% 
      gsub(" [0-9]+.", "", .) %>% gsub(" +", " ", .) %>% gsub("^ (.*) $", "\\1", .),

    x %>% html_nodes("span.genre") %>% html_text() %>% 
      gsub("\n", "", .) %>% gsub(" +", " ", .) %>% gsub("^ (.*) $", "\\1", .),

    x %>% html_nodes("span.runtime") %>% html_text() %>% gsub("\n", "", .) %>% 
      gsub(" +", " ", .) %>% gsub("^ (.*) $", "\\1", .) %>% ifelse(length(.)==0, NA, .),

    x %>% html_nodes("div.ipl-rating-widget") %>% 
      html_nodes("div.ipl-rating-star.small") %>% 
      html_text("span.ipl-rating-star__rating") %>% 
      gsub("\n", "", .) %>% gsub(" +", " ", .) %>% gsub("^ (.*) $", "\\1", .) %>% 
      as.numeric() %>%(function(.x) {if (length(.x)!=2) {c(NA, NA)} else {.x}})
  ) %>% 
    `names<-`(c("title", "genres", "runtime", "imdbrating", "userating"))
}
movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>% 
  read_html() %>% 
  html_nodes("div#ratings-container.lister-list") %>% 
  html_nodes("div.lister-item.mode-detail") %>% 
  lapply(., get_movies_info) %>% 
  do.call("bind_rows", .)
ratings_agg <- read_csv("materials/ratings.csv") %>%
  group_by(`Date Rated`) %>%
  summarise(
    avg_rating = mean(`Your Rating`),
    n_rating = n()
  ) %>%
  right_join(
    tibble(
      "Date Rated" = seq(
        from = as.Date("2016-01-01"), 
        to = as.Date(paste0(lubridate::year(Sys.Date()), "-12-31")), 
        by = 1
      )
    )
  ) %>%
  mutate(
    Year = lubridate::year(`Date Rated`),
    Month = lubridate::month(`Date Rated`, label = TRUE, abbr = FALSE),
    wDay = lubridate::wday(`Date Rated`, label = TRUE, abbr = FALSE, week_start = 1),
    wDay = factor(wDay, levels = rev(levels(wDay))),
    Week = lubridate::isoweek(`Date Rated`),
    n_rating = ifelse(is.na(n_rating), 0, n_rating)
  ) %>% 
  filter(Year == 2017)
x_labels <- ratings_agg %>% 
  select(Week, Month) %>% 
  distinct() %>% 
  group_by(Month) %>% 
  summarise(Week = min(Week)+2)
p <- ratings_agg %>% 
  ggplot(aes(x = Week, y = wDay, fill = avg_rating, label = n_rating)) +
    geom_tile(colour = "grey20", size = 0.1) +
    geom_text(colour = "white", fontface = "bold", size = 3) +
    facet_wrap(~ Year, ncol = 1) +
    scale_fill_viridis(name = "Average", limits = c(0, 10), na.value = "grey30") +
    scale_y_discrete(expand = c(0, 0)) +
    scale_x_continuous(
      expand = c(0, 0), 
      breaks = x_labels[["Week"]], 
      labels = x_labels[["Month"]]
    ) +
    theme(
      axis.ticks = element_blank(),
      axis.text.x = element_text(angle = 45, hjust = 1),
      axis.title = element_blank(),
      panel.grid = element_blank()
    )
p <- read_csv("materials/ratings.csv") %>% 
  select(`Your Rating`, `IMDb Rating`) %>% 
  gather(data = ., key = Who, value = Rating) %>% 
  ggplot(aes(x = round(Rating, digits = 0), fill = Who)) +
    geom_density(
      aes(x = Rating, y = (..count../sum(..count..))*100),
      bw = 1, 
      alpha = 0.75, 
      colour = "white"
    ) +
    geom_histogram(
      aes(y = ..count../sum(..count..)),
      binwidth = 0.5,
      colour = "white",
      position = position_dodge(preserve = "single")
    ) +
    scale_x_continuous(name = "Rating", expand = c(0, 0), limits = c(0, 10), breaks = c(0, seq_len(10))) +
    scale_y_continuous(expand = c(0, 0), labels = percent, limits = c(0, 0.30)) +
    scale_fill_viridis(name = NULL, discrete = TRUE) +
    labs(x = "Rating", y = "Proportion", title = "Distribution of Ratings") +
    theme(legend.position = c(0, 1), legend.justification = c(-0.05, 1.05))

Les Bases de Données et R

Préparer sa session

Trucs et astuces

Le tidyverse

Qu'est-ce-que le tidyverse ?

Qu'est-ce-que le tidyverse ?

tibble

comme un data.frame, mais en mieux !

Pré-requis

Construire un tibble

Construire un tibble

Afficher un data.frame

Afficher un tibble

Afficher un tibble

Afficher un tibble : les options

Sélectionner une variable : $, [ et [[

Sélectionner une variable : $, [ et [[

Assurer la rétro-compatibilité du code

Exercices (Partie I)

Exercices (Partie I)

Exercices (Partie I)

Exercices (Partie I)

Exercices (Partie II)

Exercices (Partie II)

Exercices (Partie II)

Pour aller plus loin …

%>%

magrittr : "Ceci n'est pas un pipe."

Pré-requis

Qu'est-ce-que le "pipe" ?

Pourquoi utiliser le "pipe" ?

Conserver les étapes

Remplacer l'objet original

Composer l'appel aux fonctions

Utiliser le "pipe"

Pour aller plus loin …

readr & readxl

Importer des données

Pré-requis

Les fonctions de base de readr

Par rapport à celles de R : Entrée

Par rapport à celles de R : Sortie

Dans la pratique

Lire un CSV avec base

Dans la pratique

Lire un CSV avec readr

Dans la pratique

Lire un "texte" avec base

Dans la pratique

Lire un "texte" avec readr

Les arguments de readr

Les arguments de readr

Les arguments de readr

Exercices

Exercices

Exercices

Exercices

Les alternatives à readr

Quelques benchmark !

Quelques benchmark : Lecture

Quelques benchmark : Ecriture

Exporter des données vers un fichier

dplyr

Une grammaire de la manipulation des données

dplyr : Le pipe et des fonctions utiles

Pré-requis

Les fonctions de base

Les fonctions de base

Sélectionner des lignes

Exercices

Exercices

Exercices

Exercices

Exercices

Réordonner des lignes

Exercices

Exercices

Sélectionner des colonnes

Exercices

Exercices

Sélectionner une variable : `$`, `[` et `[[`

Sélectionner une variable : `$`, `[` et `[[`

Utiliser `group_by()`

dplyr > SQL > tbl ? > `collect()` > tbl

dplyr > SQL > tbl ? > `collect()` > tbl

dplyr, `explain()` to me